#!/usr/bin/env python3

import pandas as pd 
from openpyxl import load_workbook
from openpyxl import Workbook
from copy import copy
from openpyxl.utils import get_column_letter,column_index_from_string

import re
pattern = re.compile(r'(\w+)(\d+)')

def convert_letter_pos_to_num(pos_str):
    pos_str = pos_str.strip()
    result = re.match(pattern,pos_str)
    if result:
        row = int(result.group(2))
        col = column_index_from_string(result.group(1))
        return row,col
    else:
        return None

def convert_num_pos_to_letter(pos):
    return '{}{}'.format(get_column_letter(pos[1]),pos[0])

def convert_letter_range_to_num_range(range_str):
    range_str = str(range_str).strip().split(':')
    start_pos = convert_letter_pos_to_num(range_str[0])
    end_pos = convert_letter_pos_to_num(range_str[1])
    return start_pos,end_pos

def convert_num_range_to_letter_range(range):
    start_str = convert_num_pos_to_letter(range[0])
    end_str = convert_num_pos_to_letter(range[1])
    return '{}:{}'.format(start_str,end_str)

def test_range_in(cur_range,test_range):
    if (cur_range[0][0] <= test_range[0][0] and 
        cur_range[0][1] <= test_range[0][1] and
        cur_range[1][0] >= test_range[1][0] and
        cur_range[1][1] >= test_range[1][1]):
        return True
    else:
        return False

def get_pos_to_tag_delta(src_rect,tag_rect):
    src_row_start = src_rect[0][0]
    src_col_start = src_rect[0][1]

    tag_row_start = tag_rect[0][0]
    tag_col_start = tag_rect[0][1]

    delta_row = tag_row_start - src_row_start
    delta_col = tag_col_start - src_col_start

    return delta_row,delta_col

def get_new_pos_with_delta(src_rect,delta):
    src_row_start = src_rect[0][0]
    src_col_start = src_rect[0][1]
    src_row_end = src_rect[1][0]
    src_col_end = src_rect[1][1]

    tag_row_start = src_row_start + delta[0]
    tag_col_start = src_col_start + delta[1]
    tag_row_end = src_row_end + delta[0]
    tag_col_end = src_col_end + delta[1]

    return ((tag_row_start,tag_col_start),(tag_row_end,tag_col_end))

def get_alph_by_num(num):
    if num < 27:
        c =chr(num+64).upper()    #ASCII字符,chr(65)='A'
    else:
        if num < 677:
            c = chr(divmod(i_col,26)[0]+64)+chr(divmod(i_col,26)[1]+64)
        else:
            c = chr(divmod(i_col,676)[0]+64) + chr(divmod(divmod(i_col,676)[1],26)[0]+64) + chr(divmod(divmod(i_col,676)[1],26)[1]+64)
    return c


class PandasExcel():
    def __init__(self,path,head_part=None,tail_part=None,table_title=None,header=None,**kwargs):
        self.path = path
        self.kwargs = kwargs
        self.hd_part = head_part
        self.tl_part = tail_part

        if not table_title:
            if not head_part:
                self.tb_title = None
            else:
                self.tb_title = head_part[0]
        else:
            self.tb_title = table_title

        if not header:
            if not head_part:
                raise ValueError('head_part和header不应该同时为空！')
            self.hdr = head_part[-1][0] + 1
        else:
            self.hdr = header
            if header == 2 and not self.tb_title:
                self.tb_title = (1,1)

        self.getTableDate()

        #self.data = pd.read_excel(path,header=self.hdr-1,**kwargs)
        #self.wb = load_workbook(path)

        
        self.getMergeList()

    def getRowFromWs(self,row_index,column_len):
        ret_list = []
        for i in range(1,column_len+1):
            value = self.ws.cell(row=row_index,column=i).value
            ret_list.append(value)
        return ret_list

    def getTableDate(self):
        
        self.wb = load_workbook(self.path)

        if 'sheet_name' in self.kwargs:
            self.ws = self.wb[self.kwargs['sheet_name']]
        else:
            self.ws = self.wb.active

        self._ws_max_row = self.ws.max_row
        self._ws_max_column = self.ws.max_column

        if self.tl_part:
            if self.tl_part[0][0] > self._ws_max_row:
                self.tl_part = None
            else:
                self._ws_max_row = self.tl_part[0][0] - 1

        self._ws_columns = self.getRowFromWs(self.hdr,self._ws_max_column)
        count = 0
        for index,col in enumerate(self._ws_columns):
            if not col:
                self._ws_columns[index] = 'Unknow'+ str(count)
                count += 1
        self.data = pd.DataFrame(columns=self._ws_columns)

        count = 0
        for row_i in range(self.hdr+1,self._ws_max_row):
            self.data.loc[count,:] = self.getRowFromWs(row_i,self._ws_max_column)
            count += 1

        #self.data = pd.DataFrame(ret_data)
        if 'index_col' in self.kwargs:
            self.data = self.data.set_index(self._ws_columns[self.kwargs['index_col']])


        #首先获取全部列的列标题
        #
        #遍历读取所有数据
        #
        #构造pandas数据结构

    def getPandasData(self):
        return self.data

    def getActivateWorkBook(self):
        return self.ws

    def writeHeader(self,tag_ws,delta):
        self.copyLineStyleWithColumn(tag_ws,(self.hdr+delta[0],delta[1]+1))
        col_count = 1
        for hd in self.getTableColumns():
            tag_ws.cell(row=self.hdr+delta[0],column=col_count+delta[1]).value = hd
            col_count += 1

    def copyLineStyleWithColumn(self,tag_ws,start_pos):
        i_row = self.hdr
        tag_row_start = start_pos[0]
        tag_col_start = start_pos[1]
        tag_ws.row_dimensions[tag_row_start].height = self.ws.row_dimensions[i_row].height
        for col in range(1,len(self.getTableColumns())+1):
            src_cell = self.ws.cell(row=i_row,column=col)

            try:
                if src_cell.has_style:
                    tag_ws.cell(row=tag_row_start,column=col+start_pos[1]-1).font = copy(src_cell.font)
                    tag_ws.cell(row=tag_row_start,column=col+start_pos[1]-1).border = copy(src_cell.border)
                    tag_ws.cell(row=tag_row_start,column=col+start_pos[1]-1).fill = copy(src_cell.fill)
                    tag_ws.cell(row=tag_row_start,column=col+start_pos[1]-1).number_format = copy(src_cell.number_format)
                    tag_ws.cell(row=tag_row_start,column=col+start_pos[1]-1).protection = copy(src_cell.protection)
                    tag_ws.cell(row=tag_row_start,column=col+start_pos[1]-1).alignment = copy(src_cell.alignment)
            except AttributeError as e:
                print("cell(%s)->cell(%s) is %s" % (src_index,tag_index,e))
                continue

    def writePandasData(self,tag_ws,delta):
        #首先写入头部
        if self.ws != tag_ws:
            self.writeHeader(tag_ws,delta)

        tag_row_start = self.hdr+1+delta[0]
        tag_col_start = 1 + delta[1]

        row_count = tag_row_start
        col_count = tag_col_start
        column_len = len(self.getTableColumns())

        for index,ever_row in self.data.iterrows():
            col_count = tag_col_start
            #考虑是否存在index
            self.copyLineStyleWithColumn(tag_ws,(row_count,col_count))
            if 'index_col' in self.kwargs:
                tag_ws.cell(row=row_count,column=col_count).value = index
                col_count += 1

            for item in ever_row.to_list():
                tag_ws.cell(row=row_count,column=col_count).value = item
                col_count += 1
            row_count += 1

    def getMergeList(self):
        self.merge_lists = []
        for merge_cell in self.ws.merged_cells:
            merge_range = convert_letter_range_to_num_range(merge_cell)
            self.merge_lists.append(merge_range)

    def getTableTitle(self):
        if self.tb_title:
            return self.ws.cell(row=self.tb_title[0],column=self.tb_title[1]).value

    def getTableColumns(self):
        i_row = self.hdr
        columns = []
        for col in range(1,self.ws.max_column+1):
            item = self.ws.cell(row=i_row,column=col).value
            columns.append(item)
        return columns

    def copy_column_width(self,src_ws,tag_ws):
        max_col = src_ws.max_row
        for c in range(1,max_col+1):
            tag_ws.column_dimensions[get_column_letter(c)].width = src_ws.column_dimensions[get_column_letter(c)].width

    def copy_range(self,src_ws,tag_ws,src_rect,tag_rect):
        src_row_start = src_rect[0][0]
        src_col_start = src_rect[0][1]
        src_row_end = src_rect[1][0]
        src_col_end = src_rect[1][1]
        tag_row_start = tag_rect[0][0]
        tag_col_start = tag_rect[0][1]
        tag_row_end = tag_rect[1][0]
        tag_col_end = tag_rect[1][1]

        delta = get_pos_to_tag_delta(src_rect,tag_rect)

        #检测要拷贝的区域中是否存在合并单元格
        for ever_merge_cell in self.merge_lists:
            if test_range_in(src_rect,ever_merge_cell):
                print(ever_merge_cell)
                new_merge = get_new_pos_with_delta(src_rect,delta)
                #print(new_merge)
                tag_ws.merge_cells(convert_num_range_to_letter_range(new_merge))

        for i_row in range(src_row_start,src_row_end+1):
            tag_ws.row_dimensions[i_row + delta[0]].height = src_ws.row_dimensions[i_row].height
            for i_col in range(src_col_start,src_col_end+1):
                src_index = '%s%d'%(get_column_letter(i_col),i_row)
                tag_index = '%s%d'%(get_column_letter(i_col+delta[1]),i_row+delta[0])  #单元格编号
                try:
                    src_cell = src_ws[src_index]
                    tag_ws[tag_index].value = src_cell.value
                    if src_cell.has_style:
                        tag_ws[tag_index].font = copy(src_cell.font)
                        tag_ws[tag_index].border = copy(src_cell.border)
                        tag_ws[tag_index].fill = copy(src_cell.fill)
                        tag_ws[tag_index].number_format = copy(src_cell.number_format)
                        tag_ws[tag_index].protection = copy(src_cell.protection)
                        tag_ws[tag_index].alignment = copy(src_cell.alignment)
                except AttributeError as e:
                    print("cell(%s)->cell(%s) is %s" % (src_index,tag_index,e))
                    continue

    def save(self,path=None):
        if path:
            nwb = Workbook()
            nws = nwb.active
            
            #设置列宽
            self.copy_column_width(self.ws,nws)
            
            #先写入头部
            if self.hd_part:
                src_rect = self.hd_part
                tag_rect = src_rect
                
                #拷贝头部信息，包括表标题
                self.copy_range(self.ws,nws,src_rect,tag_rect)
            else:
                if self.tb_title:
                    #写入标题
                    src_rect = (self.tb_title,(self.tb_title[0],self.tb_title[1]+len(self.getTableColumns())-1))
                    tag_rect = src_rect
                    #拷贝头部信息，包括表标题
                    self.copy_range(self.ws,nws,src_rect,tag_rect)
                else:
                    pass
                    #直接写数据
            
            #写入数据
            self.writePandasData(nws,(0,0))
            
            #写入尾部
            if self.tl_part:
                src_rect = self.tl_part
                tag_rect = src_rect
                
                #拷尾部部信息，包括表标题
                self.copy_range(self.ws,nws,src_rect,tag_rect)
            
            nwb.save(path)
        else:
            if self.tb_title:
                #写入标题
                src_rect = (self.tb_title,(self.tb_title[0],self.tb_title[1]+len(self.getTableColumns())-1))
                tag_rect = src_rect
                #拷贝头部信息，包括表标题
                self.copy_range(self.ws,self.ws,src_rect,tag_rect)
            #直接填充文件
            #写入数据
            self.writePandasData(self.ws,(0,0))
            
            #写入尾部
            if self.tl_part:
                src_rect = self.tl_part
                tag_rect = src_rect
                
                #拷尾部部信息，包括表标题
                self.copy_range(self.ws,nws,src_rect,tag_rect)

            self.save(self.path)

    def close(self):
        self.wb.close()

    def show(self):
        print(self.data.head())
        #print(self.merged_list)
        #print(type(self.merged_list))

if __name__ == "__main__":
    #pe = PandasExcel('F:\\mytools\\data\\一卡通账号参考.xlsx',[(1,1),(1,7)],index_col=0,converters={'身份证号码':str,'一卡通账号':str})
    pe = PandasExcel('F:\\mytools\\data\\一卡通账号参考.xlsx',header=2,index_col=0)
    pe.show()
    print(pe.getTableTitle())
    print(pe.getTableColumns())
    data = pe.getPandasData()
    print(data)
    data.loc[5,['状态']] = 'xxx'
    print(data.loc[5,['状态']].values[0])
    #pe.save()
    print(convert_letter_pos_to_num('A1'))
    print(convert_letter_range_to_num_range('A1:G1'))
    pe.close()

